<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class User_model extends CI_Model {

	function __construct()
	{
		parent::__construct();
    }

	function getDataLogin($username)
	{
	    $query = "SELECT * FROM view_user WHERE USERNAME='$username' AND SUB_SYSTEM='SIAB'";

        $query = $this->db->query($query);

        return $query;
	}

    function getViewKontrolData($kodeProdi)
    {
      $query = "SELECT * FROM view_kontrol WHERE KODE_PRODI =".$kodeProdi;
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }
	
	function getViewDetailNeraca($nim)
    {
      $query = "SELECT * FROM view_data_neraca WHERE `NIM` = '$nim' AND `STATUS`!='1'";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->result();
        return $data;
      }
      else
        return FALSE;
    }

    function getViewDetailNeracaDistinct($nim)
    {
      $query = "SELECT * FROM view_data_neraca WHERE `NIM` = '$nim' AND `STATUS`!='1' GROUP BY KODE_TAGIHAN, KODE_TAHUN, KODE_SEMESTER";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->result();
        return $data;
      }
      else
        return FALSE;
    }

	function getViewBiodata($nim)
    {
      $query = "SELECT * FROM view_data_mahasiswa WHERE `NIM` = '$nim'";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }

    function getTagihanSkripsi($nim)
    {
      $query = "SELECT * FROM transaksi_tagihan_lain WHERE `NIM` = '$nim' AND `KODE_TAGIHAN`=1";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        return FALSE;
      }
      else
        return TRUE;
    }

    function getTagihanKkn($nim)
    {
      $query = "SELECT * FROM transaksi_tagihan_lain WHERE `NIM` = '$nim' AND `KODE_TAGIHAN`=2";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        return FALSE;
      }
      else
        return TRUE;
    }

    function getTagihanPpl($nim)
    {
      $query = "SELECT * FROM transaksi_tagihan_lain WHERE `NIM` = '$nim' AND `KODE_TAGIHAN`=3";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        return FALSE;
      }
      else
        return TRUE;
    }

    function getNominalTagihan($kode)
    {
      $query = "SELECT * FROM tagihan_lain WHERE `ID` = '$kode'";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return 0;
    }
	
	function updateDataNeraca($id,$ref)
    {
        $query = "UPDATE neraca SET `STATUS`=1, `REFERENSI`='$ref' WHERE `ID_NERACA`='$id' ";
        $query = $this->db->query($query);
    }

    function updateDataNeracaRelatedAngsuran($kode_tagihan,$tahun,$semester,$nim,$ref)
    {
        $query = "UPDATE neraca SET `STATUS`=1, `REFERENSI`='$ref' WHERE NIM='$nim' AND `TAGIHAN`=$kode_tagihan AND TAHUN='$tahun' AND SEMESTER=$semester";
        $query = $this->db->query($query);
    }
	
	function updateStatusMahasiswa($nim,$status)
    {
        $query = "UPDATE mahasiswa SET `STATUS`='$status' WHERE `NIM`='$nim' ";
        $query = $this->db->query($query);
    }

    function insertTagihanLain($nim,$tagihan)
    {
        $query = "INSERT INTO transaksi_tagihan_lain VALUES (
					'', '{$nim}', $tagihan)";
        $query = $this->db->query($query);
    }

    function totalTagihanAngsuran($nim,$id_tagihan,$kode_tahun,$kode_semester)
    {
      $query = "SELECT SUM(JUMLAH) AS JUMLAH FROM view_data_neraca WHERE `NIM` = '$nim' AND KODE_TAGIHAN=$id_tagihan AND KODE_TAHUN='$kode_tahun' AND KODE_SEMESTER=$kode_semester";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }

    function totalPotonganAngsuran($nim,$id_tagihan,$kode_tahun,$kode_semester)
    {
      $query = "SELECT SUM(POTONGAN) AS POTONGAN FROM potongan WHERE `NIM` = '$nim' AND ID_TAGIHAN=$id_tagihan AND TAHUN='$kode_tahun' AND SEMESTER=$kode_semester";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }
    
}

